﻿/*
 * patient表的数据访问层
 * */
using System;
using System.Collections.Generic;
using System.Text;
using WH.NurseSys.Model;
using System.Data.SqlClient;
using System.Data;

namespace WH.NurseSys.DAL
{
    public class PatientDAL
    {
        public bool Insert(Patient patient)
        {
            string sqlText = "insert into patients (id,name,gender,age,age_unit,birthdate,inpatient_no,bed_no,dept_id,doctor,clinic_diag,";
            sqlText += "in_date,out_date,in_hosp) values(@id,@name,@gender,@age,@age_unit,@birthdate,@inpatient_no,@bed_no,@dept_id,@doctor,";
            sqlText += "@clinic_diag,@in_date,@out_date,@in_hosp)";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@id",patient.Id),
                new SqlParameter("@name",SqlHelper.ToDbValue(patient.Name)),
                new SqlParameter("@gender",patient.Gender),
                new SqlParameter("@age",patient.Age),
                new SqlParameter("@age_unit",patient.AgeUnit),
                new SqlParameter("@birthdate",DBNull.Value),
                new SqlParameter("@inpatient_no",SqlHelper.ToDbValue(patient.InpatientNo)),
                new SqlParameter("@bed_no",SqlHelper.ToDbValue(patient.BedNo)),
                new SqlParameter("@dept_id",patient.DeptId),
                new SqlParameter("@doctor",SqlHelper.ToDbValue(patient.Doctor)),
                new SqlParameter("@clinic_diag",SqlHelper.ToDbValue(patient.ClinicDiag)),
                new SqlParameter("@in_date",patient.InDate),
                new SqlParameter("@out_date",patient.OutDate),
                new SqlParameter("@in_hosp",patient.InHosp),
            };
            int rows = SqlHelper.ExecuteNoQuery(sqlText, parameters);
            if (rows > 0)
                return true;
            else
                return false;
        }

        public bool Delete(Guid id)
        {
            string sqlText = "delete from patients where id=@id";
            SqlParameter parameters = new SqlParameter("@id", id);
            int rows = SqlHelper.ExecuteNoQuery(sqlText, parameters);
            if (rows > 0)
                return true;
            else
                return false;
        }

        //获取一个科室内的所有病人列表
        public List<Patient> GetListByDept(Guid deptId)
        {
            string sqlText = "select id,name,gender,age,age_unit,birthdate,inpatient_no,bed_no,dept_id,doctor,clinic_diag,in_date,out_date,in_hosp from patients where dept_id=@dept_id order by in_date desc";
            SqlParameter parameters = new SqlParameter("@dept_id", deptId);
            List<Patient> patientList = new List<Patient>();
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText, parameters);
            DeptDAL deptDAL = new DeptDAL();
            foreach (DataRow dbRow in dbTable.Rows)
            {
                Patient patient = new Patient();
                patient.Id = (Guid)dbRow["id"];
                patient.Name = dbRow["name"] == DBNull.Value ? null : dbRow["name"].ToString();
                patient.Gender = Convert.ToBoolean(dbRow["gender"]);
                patient.Age = (int)dbRow["age"];
                patient.AgeUnit = Convert.ToString(dbRow["age_unit"]);
                patient.Birthdate = DateTime.Now;
                patient.InpatientNo = dbRow["inpatient_no"] == DBNull.Value ? null : dbRow["inpatient_no"].ToString();
                patient.BedNo = dbRow["bed_no"] == DBNull.Value ? null : dbRow["bed_no"].ToString();
                patient.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(patient.DeptId);
                patient.DeptName = dept != null ? dept.Name : null;
                patient.Doctor = dbRow["doctor"] == DBNull.Value ? null : dbRow["doctor"].ToString();
                patient.ClinicDiag = dbRow["clinic_diag"] == DBNull.Value ? null : dbRow["clinic_diag"].ToString();
                patient.InDate = (DateTime)dbRow["in_date"];
                patient.OutDate = (DateTime)dbRow["out_date"];
                patient.InHosp = Convert.ToBoolean(dbRow["in_hosp"]);
                patientList.Add(patient);
            }
            return patientList;
        }

        //获取一个科室内的所有在院病人列表
        public List<Patient> GetInListByDept(Guid deptId)
        {
            string sqlText = "select id,name,gender,age,age_unit,birthdate,inpatient_no,bed_no,dept_id,doctor,clinic_diag,in_date,out_date,in_hosp from patients where in_hosp=1 and dept_id=@dept_id order by in_date desc";
            SqlParameter parameters = new SqlParameter("@dept_id", deptId);
            List<Patient> patientList = new List<Patient>();
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText, parameters);
            DeptDAL deptDAL = new DeptDAL();
            foreach (DataRow dbRow in dbTable.Rows)
            {
                Patient patient = new Patient();
                patient.Id = (Guid)dbRow["id"];
                patient.Name = dbRow["name"] == DBNull.Value ? null : dbRow["name"].ToString();
                patient.Gender = Convert.ToBoolean(dbRow["gender"]);
                patient.Age = (int)dbRow["age"];
                patient.AgeUnit = Convert.ToString(dbRow["age_unit"]);
                patient.Birthdate = DateTime.Now;
                patient.InpatientNo = dbRow["inpatient_no"] == DBNull.Value ? null : dbRow["inpatient_no"].ToString();
                patient.BedNo = dbRow["bed_no"] == DBNull.Value ? null : dbRow["bed_no"].ToString();
                patient.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(patient.DeptId);
                patient.DeptName = dept != null ? dept.Name : null;
                patient.Doctor = dbRow["doctor"] == DBNull.Value ? null : dbRow["doctor"].ToString();
                patient.ClinicDiag = dbRow["clinic_diag"] == DBNull.Value ? null : dbRow["clinic_diag"].ToString();
                patient.InDate = (DateTime)dbRow["in_date"];
                patient.OutDate = (DateTime)dbRow["out_date"];
                patient.InHosp = Convert.ToBoolean(dbRow["in_hosp"]);
                patientList.Add(patient);
            }
            return patientList;
        }

        public List<Patient> GetList()
        {
            string sqlText = "select id,name,gender,age,age_unit,birthdate,inpatient_no,bed_no,dept_id,doctor,clinic_diag,in_date,out_date,in_hosp from patients";          
            List<Patient> patientList = new List<Patient>();
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText);
            DeptDAL deptDAL = new DeptDAL();
            foreach (DataRow dbRow in dbTable.Rows)
            {
                Patient patient = new Patient();
                patient.Id = (Guid)dbRow["id"];
                patient.Name = dbRow["name"] == DBNull.Value ? null : dbRow["name"].ToString();
                patient.Gender = Convert.ToBoolean(dbRow["gender"]);
                patient.Age = (int)dbRow["age"];
                patient.AgeUnit = dbRow["age_unit"].ToString();
                patient.Birthdate = DateTime.Now;
                patient.InpatientNo = dbRow["inpatient_no"] == DBNull.Value ? null : dbRow["inpatient_no"].ToString();
                patient.BedNo = dbRow["bed_no"] == DBNull.Value ? null : dbRow["bed_no"].ToString();
                patient.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(patient.DeptId);
                patient.DeptName = dept != null ? dept.Name : null;
                patient.Doctor = dbRow["doctor"] == DBNull.Value ? null : dbRow["doctor"].ToString();
                patient.ClinicDiag = dbRow["clinic_diag"] == DBNull.Value ? null : dbRow["clinic_diag"].ToString();
                patient.InDate = (DateTime)dbRow["in_date"];
                patient.OutDate = (DateTime)dbRow["out_date"];
                patient.InHosp = Convert.ToBoolean(dbRow["in_hosp"]);
                patientList.Add(patient);
            }
            return patientList;
        }

        //按检索条件进行分页检索病人列表
        public List<Patient> GePagedQueryList(int pageIndex, int pageSize, PatientSearchArg searchArg)
        {
            int rowStart = (pageIndex - 1) * pageSize;
            int rowEnd = pageIndex * pageSize;
            string sqlText = "select * from (select row_number() over (order by in_date desc) as rownum,id,name,gender,age,age_unit,birthdate,inpatient_no,bed_no,dept_id,doctor,clinic_diag,in_date,out_date,in_hosp from patients";            
            List<string> whereList = new List<string>();
            List<SqlParameter> paramList = new List<SqlParameter>();
            paramList.Add(new SqlParameter("@rowStart", rowStart));
            paramList.Add(new SqlParameter("@rowEnd", rowEnd));
            jionWhereList(searchArg, whereList, paramList);
            if (whereList.Count > 0)
                sqlText += " where " + string.Join(" and ", whereList.ToArray());
            sqlText += ") as t  where t.rownum>@rowStart and t.rownum<=@rowEnd";
            List<Patient> patientList = new List<Patient>();
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText,paramList.ToArray());
            DeptDAL deptDAL = new DeptDAL();
            foreach (DataRow dbRow in dbTable.Rows)
            {
                Patient patient = new Patient();
                patient.Id = (Guid)dbRow["id"];
                patient.Name = dbRow["name"] == DBNull.Value ? null : dbRow["name"].ToString();
                patient.Gender = Convert.ToBoolean(dbRow["gender"]);
                patient.Age = (int)dbRow["age"];
                patient.AgeUnit = dbRow["age_unit"].ToString();
                patient.Birthdate = DateTime.Now;
                patient.InpatientNo = dbRow["inpatient_no"] == DBNull.Value ? null : dbRow["inpatient_no"].ToString();
                patient.BedNo = dbRow["bed_no"] == DBNull.Value ? null : dbRow["bed_no"].ToString();
                patient.DeptId = (Guid)dbRow["dept_id"];
                Dept dept = deptDAL.GetById(patient.DeptId);
                patient.DeptName = dept != null ? dept.Name : null;
                patient.Doctor = dbRow["doctor"] == DBNull.Value ? null : dbRow["doctor"].ToString();
                patient.ClinicDiag = dbRow["clinic_diag"] == DBNull.Value ? null : dbRow["clinic_diag"].ToString();
                patient.InDate = (DateTime)dbRow["in_date"];
                patient.OutDate = (DateTime)dbRow["out_date"];
                patient.InHosp = Convert.ToBoolean(dbRow["in_hosp"]);
                patientList.Add(patient);
            }
            return patientList;
        }

        //获取符合检索条件的记录行数
        public int GetQueryCount(PatientSearchArg searchArg)
        {
            string sqlText = "select count(id) from patients";
            List<string> whereList = new List<string>();
            List<SqlParameter> paramList = new List<SqlParameter>();           
            jionWhereList(searchArg, whereList, paramList);
            if (whereList.Count > 0)
                sqlText += " where " + string.Join(" and ", whereList.ToArray());
            object scalar = SqlHelper.ExecuteScalar(sqlText, paramList.ToArray());
            return Convert.ToInt32(scalar);
        }

        //组合条件
        void jionWhereList(PatientSearchArg searchArg, List<string> whereList, List<SqlParameter> paramList)
        {
            if (searchArg.DeptId != Guid.Empty)
            {
                whereList.Add(" dept_id=@dept_id ");
                paramList.Add(new SqlParameter("@dept_id", searchArg.DeptId));
            }
            if (searchArg.InHospStatus!=null && searchArg.InHospStatus.Length <= 1)
            {
                whereList.Add(" in_hosp=@in_hosp ");
                paramList.Add(new SqlParameter("@in_hosp", searchArg.InHospStatus[0]));
            }
            if (!string.IsNullOrEmpty(searchArg.Name) && searchArg.Name != string.Empty)
            {
                whereList.Add(" name like @name ");
                paramList.Add(new SqlParameter("@name", "%" + searchArg.Name + "%"));
            }
        }

        public bool Update(Patient patient)
        {
            string sqlText = "update patients set name=@name,gender=@gender,age=@age,age_unit=@age_unit,birthdate=@birthdate,";
            sqlText += "inpatient_no=@inpatient_no,bed_no=@bed_no,dept_id=@dept_id,doctor=@doctor,clinic_diag=@clinic_diag,in_date=@in_date,";
            sqlText += "out_date=@out_date,in_hosp=@in_hosp where id=@id";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@id",patient.Id),
                new SqlParameter("@name",SqlHelper.ToDbValue(patient.Name)),
                new SqlParameter("@gender",patient.Gender),
                new SqlParameter("@age",patient.Age),
                new SqlParameter("@age_unit",patient.AgeUnit),
                new SqlParameter("@birthdate",DBNull.Value),
                new SqlParameter("@inpatient_no",SqlHelper.ToDbValue(patient.InpatientNo)),
                new SqlParameter("@bed_no",SqlHelper.ToDbValue(patient.BedNo)),
                new SqlParameter("@dept_id",patient.DeptId),
                new SqlParameter("@doctor",SqlHelper.ToDbValue(patient.Doctor)),
                new SqlParameter("@clinic_diag",SqlHelper.ToDbValue(patient.ClinicDiag)),
                new SqlParameter("@in_date",patient.InDate),
                new SqlParameter("@out_date",patient.OutDate),
                new SqlParameter("@in_hosp",patient.InHosp),
            };
            int rows = SqlHelper.ExecuteNoQuery(sqlText, parameters);
            if (rows > 0)
                return true;
            else
                return false;
        }

        public Patient GetById(Guid id)
        {
            string sqlText = "select * from patients where id=@id";
            SqlParameter parameters = new SqlParameter("@id", id);
            DataTable dbTable = SqlHelper.ExecuteTable(sqlText, parameters);
            if (dbTable.Rows.Count <= 0)
                return null;
            DataRow dbRow = dbTable.Rows[0];
            Patient patient = new Patient();
            patient.Id = new Guid(dbRow["id"].ToString());
            patient.Age = (int)dbRow["age"];
            patient.AgeUnit = dbRow["age_unit"].ToString();
            patient.BedNo = dbRow["bed_no"] == DBNull.Value ? null : dbRow["bed_no"].ToString();
            patient.ClinicDiag = dbRow["clinic_diag"] == DBNull.Value ? null : dbRow["clinic_diag"].ToString();
            patient.DeptId = new Guid(dbRow["dept_id"].ToString());
            patient.DeptName = new DeptDAL().GetById(patient.DeptId).Name;
            patient.Doctor = dbRow["doctor"] == DBNull.Value ? null : dbRow["doctor"].ToString();
            patient.Gender = Convert.ToBoolean(dbRow["gender"]);
            patient.InDate = Convert.ToDateTime(dbRow["in_date"]);
            patient.InHosp = Convert.ToBoolean(dbRow["in_hosp"]);
            patient.InpatientNo = dbRow["inpatient_no"] == DBNull.Value ? null : dbRow["inpatient_no"].ToString();
            patient.Name = dbRow["name"] == DBNull.Value ? null : dbRow["name"].ToString();            
            return patient;
        }
    }
}
